import xlrd
import pymssql
import datetime
# 连接本地sql server         地址          用户名   密码     数据库
conn = pymssql.connect("192.168.117.121", "sa", "bpr@doss@2015", "Test_BmsDB_V2_20190807")
# 建立cursor
cursor = conn.cursor()
# excel文件
fname = "C:\\Users\\clq\\Desktop\\test1111.xlsx"
#打开文件
bk = xlrd.open_workbook(fname)
#打开工作表
sh = bk.sheets()[0]
#获取行数
start_time=datetime.datetime.now()
sql3='['
Province = ""
StartTime = ""
# 遍历所有行
for i in range(2,sh.nrows):
    a = []
    sql = '['
    # 遍历所有列
    for j in range(2,sh.ncols):
        val = str(sh.cell(i, j).value)
        if j==4 or j>6:
            continue
        if j==5 and val: #日期处理
            val = str(xlrd.xldate.xldate_as_datetime(sh.cell(i, j).value, 0))
            if val:
                StartTime = val
            else:
                val = StartTime

        if j==2 and val:
            Province = val
        elif j==2 and not val:
            val = Province
        if not val:
            continue
        # 将excel每一列的值用，隔开
        sql += "'" + val + "'" + ','
    # 组合成sql语句(value1,value2,value3,,)
    sql2 = sql.strip(",")
    sql3 += sql2.strip()+'],'
sql3 = sql3.rstrip(",")+']'
listsql = eval(sql3)
for i,item in enumerate(listsql):
    if len(item) <3:
        listsql.remove(item)
        continue
print(listsql)
print(len(listsql))

data_list = []
commment_sql = "INSERT INTO [Tbl_Recruit_SchoolTrip] (   RecruitYear,   TripName,   Province,   SchoolName, " \
                   "  SchoolIcon,   City,   StartTime,   Description,   ManagerNumber,   ManagerName,   OrderBy,   CreateBy, " \
                   "  CreateNumber,   CreateDate  ) " \
                   " VALUES   ( %s,%s,%s,%s,%s,%s, %s,%s,%s,%s,%s,%s,%s, %s)"
num = 1
for i,item in enumerate(listsql):
    Province = item[0]
    SchoolName = item[3]
    City = item[1]
    StartTime = item[2]
    data_list.append(( '2020',    '2020年校园招聘',Province,SchoolName,'~/Content/themes/dist/img/open/school/'+SchoolName+'.png',City,StartTime,
                       '','372620',    '李国婷',num,    '彭文良',    '373667',    '2019-08-27 09:17:28.000'))
    num=num+1
print(data_list)
print(commment_sql)
cursor.executemany(commment_sql,data_list);
conn.commit()

